原文：http://blog.51cto.com/freebile/1068610

db2有图形执行计划显示工具,如果没有图形环境,如unix主机,可以生成文本的文件来显示执行计划1.如果第一次执行,请先 connect to dbname,执行db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL建立执行计划表2.db2 set current explain mode explain设置成解释模式,并不真正执行下面将发出的sql命令

3.db2 "select count(*) from DOA.ODS_SFC_SALE_BARCODE_T"  执行你想要分析的sql语句

4.db2 set current explain mode no取消解释模式5.db2exfmt -d sample  -g TIC -w -l -s % -n % -o db2exmt.out执行计划输出到文件db2exmt.out

相关参数设置可以参考(This application formats the contents of the Explain tables.Given a database name and other qualifying information, this tool will,query the Explain tables for information and format the results.

Syntax is:

db2exfmt [[-1] [-d ;] [-e ;] [-f O] [-h] [-l] [-n ;] [-o ;]          [-s ;] [-t]] [-u ; ;] [-w ;] [-# ;] [-v ;]

Input Fields:        -d ;     = database name containing packages        -e ;     = Explain table schema        -f O            = Formatting flags (O = Operator summary)        -g[x]  [O[T|F]IC] - Graph plan. x - turn off options (default is to turn them on) Options include:                             O = only generate graph                             T = Include Total Cost in graph                             F = Include First Tuple Cost in graph                             I = Include I/O Cost in graph                             C = Include Cardinality in graph                          Any combination of these options is allowed,                          except 'F' and 'T', which are mutually exclusive.        -h              = help        -l              = respect package name case        -n ;       = name of source of Explain                          request (SOURCE_NAME)        -no_map_char    = do no map a non-printable character to a '.'        -no_prompt      = do not prompt for user input        -o ;    = name of output file        -r ;  = id of explain requester        -s ;     = Schema or qualifier of source of Explain                          request (SOURCE_SCHEMA)        -t              = terminal output desired        -u ; ;  = user ID and password for connecting to database        -v ;    = Source Version of source of Explain request (default %)        -w ;  = Explain timestamp (use -1 to get newest Explain request)        -# ;    = section number in source (use zero for all sections)

        -1              = Use defaults -e % -n % -s % -v % -w -1 -# 0   If Explain schema is not supplied, the contents of the environment        variable $USER, or $USERNAME will be used as a default. If this        variable is not found, the user will be prompted for an Explain schema.

        Source name, source schema, and Explain timestamp may be supplied        in LIKE predicate form, which allows percent sign (%) and        underscore (_) to be used as pattern matching characters to select        multiple sources with one invocation.

        Prompting will occur for all fields that are not supplied or are        incompletely specified (except for the -h, -l and -no_map_char options).

        If -o is specified without a file name, and -t is not specified,        the user will be prompted for a file name (the default name is        db2exfmt.out).

        If neither -o nor -t is specified, the user will be prompted for        a file name (the default is terminal output).

        If -o and -t are both specified, then the output will be directed        to the terminal.)

6.查看输出文件分析sql的运行开销,示例输出如下 

-------------------

DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007Licensed Material - Program Property of IBMIBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION:   09.05.4SOURCE_NAME:   SQLC2G15SOURCE_SCHEMA:   NULLID  SOURCE_VERSION:  EXPLAIN_TIME:   2012-11-23-16.02.53.924371 EXPLAIN_REQUESTER:  DOAADMIN

Database Context:---------------- Parallelism:   None CPU Speed:   4.959615e-07 Comm Speed:   100 Buffer Pool size:  438463 Sort Heap size:  256 Database Heap size:  2645 Lock List size:  100 Maximum Lock List:  20 Average Applications:  1 Locks Available:  1280

Package Context:--------------- SQL Type:   Dynamic Optimization Level:  5 Blocking:   Block All Cursors Isolation Level:  Cursor Stability

---------------- STATEMENT 1  SECTION 201 ---------------- QUERYNO:   3 QUERYTAG:   CLP                  Statement Type:  Select Updatable:   No Deletable:   No Query Degree:   1

Original Statement:------------------select count(*) from DOA.ODS_SFC_SALE_BARCODE_T

Optimized Statement:-------------------SELECT Q3.$C0 FROM    (SELECT COUNT(*)    FROM       (SELECT $RID$       FROM DOA.ODS_SFC_SALE_BARCODE_T AS Q1) AS Q2) AS Q3

Access Plan:----------- Total Cost:   51435.9 Query Degree:  1

      Rows      RETURN     (   1)      Cost        I/O        |        1      GRPBY      (   2)     51435.9       2027        |     765027      IXSCAN     (   3)     51341.1       2027        |     765027  INDEX: DOA         S_SALE_T1       Q1

Extended Diagnostic Information:--------------------------------

No extended Diagnostic Information for this statement.

Plan Details:-------------

 1) RETURN: (Return Result)  Cumulative Total Cost:   51435.9  Cumulative CPU Cost:   1.53426e+09  Cumulative I/O Cost:   2027  Cumulative Re-Total Cost:  747.847  Cumulative Re-CPU Cost:  1.50787e+09  Cumulative Re-I/O Cost:  0  Cumulative First Row Cost:  51435.9  Estimated Bufferpool Buffers:  2028

  Arguments:  ---------  BLDLEVEL: (Build level)   DB2 v9.5.0.4 : s090429  HEAPUSE : (Maximum Statement Heap Usage)   80 Pages  PREPTIME: (Statement prepare time)            1 milliseconds  STMTHEAP: (Statement heap size)   6402

  Input Streams:  -------------   3) From Operator #2

    Estimated number of rows:  1    Number of columns:   1    Subquery predicate ID:   Not Applicable

    Column Names:    ------------    +Q4.$C0

 2) GRPBY : (Group By)  Cumulative Total Cost:   51435.9  Cumulative CPU Cost:   1.53426e+09  Cumulative I/O Cost:   2027  Cumulative Re-Total Cost:  747.847  Cumulative Re-CPU Cost:  1.50787e+09  Cumulative Re-I/O Cost:  0  Cumulative First Row Cost:  51435.9  Estimated Bufferpool Buffers:  2028

  Arguments:  ---------  AGGMODE : (Aggregration Mode)   COMPLETE  GROUPBYC: (Group By columns)   FALSE  GROUPBYN: (Number of Group By columns)   0  ONEFETCH: (One Fetch flag)   FALSE

  Input Streams:  -------------   2) From Operator #3

    Estimated number of rows:  765027    Number of columns:   0    Subquery predicate ID:   Not Applicable

  Output Streams:  --------------   3) To Operator #1

    Estimated number of rows:  1    Number of columns:   1    Subquery predicate ID:   Not Applicable

    Column Names:    ------------    +Q4.$C0

 3) IXSCAN: (Index Scan)  Cumulative Total Cost:   51341.1  Cumulative CPU Cost:   1.343e+09  Cumulative I/O Cost:   2027  Cumulative Re-Total Cost:  652.991  Cumulative Re-CPU Cost:  1.31662e+09  Cumulative Re-I/O Cost:  0  Cumulative First Row Cost:  50.0327  Estimated Bufferpool Buffers:  2028

  Arguments:  ---------  MAXPAGES: (Maximum pages for prefetch)   2026  PREFETCH: (Type of Prefetch)   NONE  ROWLOCK : (Row Lock intent)   NEXT KEY SHARE  SCANDIR : (Scan Direction)   FORWARD  TABLOCK : (Table Lock intent)   INTENT SHARE  TBISOLVL: (Table access Isolation Level)   CURSOR STABILITY

  Input Streams:  -------------   1) From Object DOA.S_SALE_T1

    Estimated number of rows:  765027    Number of columns:   1    Subquery predicate ID:   Not Applicable

    Column Names:    ------------    +Q1.$RID$

  Output Streams:  --------------   2) To Operator #2

    Estimated number of rows:  765027    Number of columns:   0    Subquery predicate ID:   Not Applicable

Objects Used in Access Plan:---------------------------

 Schema: DOA      Name:  ODS_SFC_SALE_BARCODE_T Type:  Table (reference only)

 Schema: DOA      Name:  S_SALE_T1 Type:  Index   Time of creation:   2011-01-15-14.03.41.217506   Last statistics update:  2012-04-23-18.45.51.142593   Number of columns:   1   Number of rows:   765027   Width of rows:    -1   Number of buffer pool pages:  50996   Distinct row values:   No   Tablespace name:   TS_INDEX_4K   Tablespace overhead:   24.100000   Tablespace transfer rate:  0.900000   Source for statistics:   Single Node   Prefetch page count:   128   Container extent page count:  32   Index clustering statistic:  97.000000   Index leaf pages:   2026   Index tree levels:   3   Index full key cardinality:  18700   Index first key cardinality:  18700   Index first 2 keys cardinality: -1   Index first 3 keys cardinality: -1   Index first 4 keys cardinality: -1   Index sequential pages:  0   Index page density:   0   Index avg sequential pages:  0   Index avg gap between sequences:0   Index avg random pages:  2026   Fetch avg sequential pages:  -1   Fetch avg gap between sequences:-1   Fetch avg random pages:  -1   Index RID count:   765027   Index deleted RID count:  0   Index empty leaf pages:  0   Base Table Schema:   DOA        Base Table Name:   ODS_SFC_SALE_BARCODE_T   Columns in index:    BILL(A)

Base Table For Index Not Already Shown:---------------------------------------

 Schema: DOA      Name:  ODS_SFC_SALE_BARCODE_T   Time of creation:   2011-01-15-13.40.52.458517   Last statistics update:  2012-04-23-18.45.51.142593   Number of data partitions:  1   Number of columns:   21   Number of rows:   765027   Number of pages:   50996   Number of pages with rows:  50996   Table overflow record count:  0   Indexspace name:   TS_INDEX_4K   Tablespace name:   TS_4K   Tablespace overhead:   24.100000   Tablespace transfer rate:  0.900000   Prefetch page count:   -1   Container extent page count:  32

   Long tablespace name:   TS_4K